package com.yss.sofa.report.engine.util;

import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.text.SimpleDateFormat;
import java.util.Collection;
import java.util.Date;
import java.util.HashMap;
import java.util.HashSet;
import java.util.LinkedHashMap;
import java.util.Map;
import java.util.Set;

import javax.print.attribute.standard.MediaSize;

import net.coobird.thumbnailator.Thumbnails;

import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.DataFormatter;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFFont;
import org.apache.poi.xssf.usermodel.XSSFFormulaEvaluator;
import org.apache.poi.xssf.usermodel.XSSFPrintSetup;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.poi.xssf.usermodel.extensions.XSSFCellBorder.BorderSide;

import com.yss.sofa.cml.color.Color;
import com.yss.sofa.cml.Document;
import com.yss.sofa.cml.text.FontKey;
import com.yss.sofa.cml.LineBox;
import com.yss.sofa.cml.LinePen;
import com.yss.sofa.cml.Padding;
import com.yss.sofa.cml.Paper;
import com.yss.sofa.cml.design.BaseCellElement;
import com.yss.sofa.cml.design.BytesImageStore;
import com.yss.sofa.cml.design.DesignBand;
import com.yss.sofa.cml.design.DesignColumnGroup;
import com.yss.sofa.cml.design.DesignDocument;
import com.yss.sofa.cml.design.DesignExpression;
import com.yss.sofa.cml.design.DesignImage;
import com.yss.sofa.cml.design.DesignLineBox;
import com.yss.sofa.cml.design.DesignPadding;
import com.yss.sofa.cml.design.DesignRow;
import com.yss.sofa.cml.design.DesignText;
import com.yss.sofa.cml.type.EvaluationTimeEnum;
import com.yss.sofa.cml.type.HorizontalAlignEnum;
import com.yss.sofa.cml.type.ImageTypeEnum;
import com.yss.sofa.cml.type.LayoutEnum;
import com.yss.sofa.cml.type.LineStyleEnum;
import com.yss.sofa.cml.type.OrientationEnum;
import com.yss.sofa.cml.type.OverflowEnum;
import com.yss.sofa.cml.type.ScaleImageEnum;
import com.yss.sofa.cml.type.VerticalAlignEnum;
import com.yss.sofa.report.engine.Report;
import com.yss.sofa.report.engine.cell.page.PageCell;
import com.yss.sofa.report.engine.cell.visible.result.ChartResultCell;
import com.yss.sofa.report.engine.cell.visible.result.FloatingResultCell;
import com.yss.sofa.report.engine.cell.visible.result.ResultCell;

public final class PrintUtil {
	private PrintUtil(){}
	private static XSSFWorkbook toExcel(Report report) throws Exception{
		ByteArrayInputStream in=new ByteArrayInputStream(report.getMiddle());
		try {
			XSSFWorkbook excel=new XSSFWorkbook(in);
			return excel;
		} finally {
			in.close();
		}
	}
	private static int getColPixelWidth(XSSFSheet sheet,int col,float fontWidth,int dpi){
		if(sheet.isColumnHidden(col)){
			return 1;
		}
		float widthIn256 = sheet.getColumnWidth(col);
		return (int)(widthIn256/256f*(fontWidth*dpi/72f)*0.75f)+1;
	}
	private static int getRowPixelHeight(XSSFSheet sheet,int row,int dpi){
		if(sheet.getRow(row).getZeroHeight()){
			return 1;
		}
		float h=sheet.getRow(row).getHeight()/15f;
		return (int)(h*dpi/72f*0.75f)+1;
	}
	private static Paper toPrintPager(short paper){
		switch (paper) {
			case XSSFPrintSetup.A3_PAPERSIZE:
				return Paper.getPaper(MediaSize.ISO.A3);
			case XSSFPrintSetup.A4_PAPERSIZE:
			case XSSFPrintSetup.A4_EXTRA_PAPERSIZE:
			case XSSFPrintSetup.A4_PLUS_PAPERSIZE:
			case XSSFPrintSetup.A4_ROTATED_PAPERSIZE:
			case XSSFPrintSetup.A4_SMALL_PAPERSIZE:
			case XSSFPrintSetup.A4_TRANSVERSE_PAPERSIZE:
				return Paper.getPaper(MediaSize.ISO.A4);
			case XSSFPrintSetup.A5_PAPERSIZE:
				return Paper.getPaper(MediaSize.ISO.A5);
			case XSSFPrintSetup.B4_PAPERSIZE:
				return Paper.getPaper(MediaSize.ISO.B4);
			case XSSFPrintSetup.B5_PAPERSIZE:
				return Paper.getPaper(MediaSize.ISO.B5);
			default:
				return Paper.getPaper(MediaSize.ISO.A4);
		}
	}
	private static OrientationEnum toPrintOrientation(boolean isLandscape){
		if(isLandscape){
			return OrientationEnum.LANDSCAPE;
		} else {
			return OrientationEnum.PORTRAIT;
		}
	}
	private static int toPrintMargin(double marign,int dpi){
		return (int)(marign*dpi);
	}
	private static LineStyleEnum toPrintBorderStyle(BorderStyle border){
		LineStyleEnum style=null;
		switch (border) {
		case NONE:
			style=null;
			break;
		case THIN:
			style=LineStyleEnum.SOLID;
			break;
		case MEDIUM:
			style=LineStyleEnum.SOLID;
			break;
		case DASHED:
			style=LineStyleEnum.DASHED;
			break;
		case DOTTED:
			style=LineStyleEnum.DOTTED;
			break;
		case THICK:
			style=LineStyleEnum.SOLID;
			break;
		case DOUBLE:
			style=LineStyleEnum.DOUBLE;
			break;
		case HAIR:
			style=LineStyleEnum.DOTTED;
			break;
		case MEDIUM_DASHED:
			style=LineStyleEnum.DASHED;
			break;
		case DASH_DOT:
			style=LineStyleEnum.DASHED;
			break;
		case MEDIUM_DASH_DOT:
			style=LineStyleEnum.DASHED;
			break;
		case DASH_DOT_DOT:
			style=LineStyleEnum.DASHED;
			break;
		case MEDIUM_DASH_DOT_DOT:
			style=LineStyleEnum.DASHED;
			break;
		case SLANTED_DASH_DOT:
			style=LineStyleEnum.DASHED;
			break;
		default:
			break;
		}
		return style;
	}
	private static Color toPrintBorderColor(XSSFCellStyle style,BorderSide side){
		Color color=new Color(0,0,0);
		if(style.getBorderColor(side)!=null){
			byte[] rgb=style.getBorderColor(side).getRGBWithTint();
			if(rgb!=null){
				color=new Color((rgb[0] & 0xff), (rgb[1] & 0xff), (rgb[2] & 0xff));
			}
		}
		return color;
	}
	private static float toPrintBorderWidth(BorderStyle border){
		int width=1;
		switch (border) {
		case NONE:
			width=1;
			break;
		case THIN:
			width=1;
			break;
		case MEDIUM:
			width=2;
			break;
		case DASHED:
			width=1;
			break;
		case DOTTED:
			width=1;
			break;
		case THICK:
			width=3;
			break;
		case DOUBLE:
			width=1;
			break;
		case HAIR:
			width=1;
			break;
		case MEDIUM_DASHED:
			width=2;
			break;
		case DASH_DOT:
			width=1;
			break;
		case MEDIUM_DASH_DOT:
			width=2;
			break;
		case DASH_DOT_DOT:
			width=1;
			break;
		case MEDIUM_DASH_DOT_DOT:
			width=2;
			break;
		case SLANTED_DASH_DOT:
			width=2;
			break;
		default:
			break;
		}
		return width;
	}
	private static LineBox toPrintBorder(XSSFCellStyle style){
		LinePen left=new LinePen();
		left.setLineColor(toPrintBorderColor(style, BorderSide.LEFT));
		left.setLineStyle(toPrintBorderStyle(style.getBorderLeftEnum()));
		left.setLineWidth(toPrintBorderWidth(style.getBorderLeftEnum()));
		
		LinePen top=new LinePen();
		top.setLineColor(toPrintBorderColor(style, BorderSide.TOP));
		top.setLineStyle(toPrintBorderStyle(style.getBorderTopEnum()));
		top.setLineWidth(toPrintBorderWidth(style.getBorderTopEnum()));
		
		LinePen right=new LinePen();
		right.setLineColor(toPrintBorderColor(style, BorderSide.RIGHT));
		right.setLineStyle(toPrintBorderStyle(style.getBorderRightEnum()));
		right.setLineWidth(toPrintBorderWidth(style.getBorderRightEnum()));
		
		LinePen bottom=new LinePen();
		bottom.setLineColor(toPrintBorderColor(style, BorderSide.BOTTOM));
		bottom.setLineStyle(toPrintBorderStyle(style.getBorderBottomEnum()));
		bottom.setLineWidth(toPrintBorderWidth(style.getBorderBottomEnum()));
		DesignLineBox _=new DesignLineBox(top,right,bottom,left);
		return _;
	}
	private static Color toPrintBackgroundColor(XSSFCellStyle style){
		Color color=new Color(255,255,255);
		if(style.getFillForegroundColor()==0){
			byte[] rgb=style.getFillForegroundColorColor().getRGBWithTint();
			color=new Color((rgb[0] & 0xff), (rgb[1] & 0xff), (rgb[2] & 0xff));
		}
		return color;
	}
	private static Color toPrintColor(XSSFCellStyle style){
		Color color=new Color(0,0,0);
		if(style.getFont().getColor()==0){
			byte[] rgb=style.getFont().getXSSFColor().getRGBWithTint();
			color=new Color((rgb[0] & 0xff), (rgb[1] & 0xff), (rgb[2] & 0xff));
		}
		return color;
	}
	private static FontKey toPrintFont(XSSFCellStyle style){
		String name=style.getFont().getFontName();
		int fontstyle=FontKey.PLAIN;
		if(style.getFont().getBold() && style.getFont().getItalic()){
			fontstyle=FontKey.BOLD_ITALIC;
		} else if (style.getFont().getBold()){
			fontstyle=FontKey.BOLD;
		} else if(style.getFont().getItalic()){
			fontstyle=FontKey.ITALIC;
		}
		int size=style.getFont().getFontHeight()/20;
		return new FontKey(name, fontstyle, size);
	}
	private static FontKey toPrintFont(XSSFFont font){
		String name=font.getFontName();
		int fontstyle=FontKey.PLAIN;
		if(font.getBold() && font.getItalic()){
			fontstyle=FontKey.BOLD_ITALIC;
		} else if (font.getBold()){
			fontstyle=FontKey.BOLD;
		} else if(font.getItalic()){
			fontstyle=FontKey.ITALIC;
		}
		int size=font.getFontHeight()/20;
		return new FontKey(name, fontstyle, size);
	}
	private static HorizontalAlignEnum toPrintHorizontalAlign(XSSFCellStyle style){
		HorizontalAlignEnum align=HorizontalAlignEnum.LEFT;
		switch (style.getAlignmentEnum()) {
		case CENTER:
			align=HorizontalAlignEnum.CENTER;
			break;
		case CENTER_SELECTION:
			align=HorizontalAlignEnum.CENTER;
			break;
		case DISTRIBUTED:
			align=HorizontalAlignEnum.LEFT;
			break;
		case FILL:
			align=HorizontalAlignEnum.LEFT;
			break;
		case GENERAL:
			align=HorizontalAlignEnum.LEFT;
			break;
		case JUSTIFY:
			align=HorizontalAlignEnum.LEFT;
			break;
		case LEFT:
			align=HorizontalAlignEnum.LEFT;
			break;
		case RIGHT:
			align=HorizontalAlignEnum.RIGHT;
			break;
		default:
			break;
		}
		return align;
	}
	private static VerticalAlignEnum toPrintVerticalAlign(XSSFCellStyle style){
		VerticalAlignEnum align=VerticalAlignEnum.MIDDLE;
		switch (style.getVerticalAlignmentEnum()) {
		case CENTER:
		case DISTRIBUTED:
		case JUSTIFY:
			align=VerticalAlignEnum.MIDDLE;
			break;
		case TOP:
			align=VerticalAlignEnum.TOP;
			break;
		case BOTTOM:
			align=VerticalAlignEnum.BOTTOM;
			break;
		default:
			break;
		}
		return align;
	}
	private static Padding toPrintPadding(XSSFCellStyle style){
		DesignPadding padding=new DesignPadding(0, 0, 0, (int) style.getIndention());
		return padding;
	}
	private static int toPrintRotation(XSSFCellStyle style){
		int degree=style.getRotation();
		return degree;
	}
	private static DesignText toPrintCell(XSSFSheet sheet,int r,int c) throws Exception{
		DesignText printText = new DesignText();
		printText.setRowspan(1);
		printText.setColspan(1);
		XSSFRow row=sheet.getRow(r);
		XSSFCell cell=row.getCell(c);
		XSSFCellStyle style=cell.getCellStyle();
		if(style!=null){
			printText.setLineBox(toPrintBorder(style));
			printText.setBackcolor(toPrintBackgroundColor(cell.getCellStyle()));
			printText.setForecolor(toPrintColor(style));
			printText.setFontKey(toPrintFont(style));
			printText.setHorizontalAlign(toPrintHorizontalAlign(style));
			printText.setVerticalAlign(toPrintVerticalAlign(style));
			printText.setPadding(toPrintPadding(style));
			printText.setRotationValue(toPrintRotation(style));
			printText.setOverflow(toPrintTextStretch(style));
		}
		return printText;
	}
	private static OverflowEnum toPrintTextStretch(XSSFCellStyle style){
		if(style.getWrapText()){
			return OverflowEnum.STRETCH;
		} else if(style.getShrinkToFit()) {
			return OverflowEnum.SHRINK;
		} else {
			return OverflowEnum.CLIP;
		}
	}
	private static DesignImage toPrintCell(XSSFSheet sheet,Map<ResultCell,byte[]> images,ResultCell result,int r,int c){
		DesignImage printImage=new DesignImage();
		XSSFRow row=sheet.getRow(r);
		XSSFCell cell=row.getCell(c);

		printImage.setRowspan(result.getRowInc());
		printImage.setColspan(result.getColInc());
		
		byte[] png=images.get(result);
		BytesImageStore store = new BytesImageStore();
		store.setImageType(ImageTypeEnum.PNG);
		store.writeBytes(png);
		printImage.setScaleImage(ScaleImageEnum.FILL);
		printImage.setImageStore(store);
		
		XSSFCellStyle style=cell.getCellStyle();
		if(style!=null){
			printImage.setLineBox(toPrintBorder(style));
			printImage.setBackcolor(toPrintBackgroundColor(cell.getCellStyle()));
			printImage.setHorizontalAlign(toPrintHorizontalAlign(style));
			printImage.setVerticalAlign(toPrintVerticalAlign(style));
			printImage.setPadding(toPrintPadding(style));
		}
		return printImage;
	}
	private static DesignText toPrintCell(XSSFSheet sheet,XSSFFormulaEvaluator evaluator,ResultCell result,int r,int c) throws Exception{
		DesignText printText = new DesignText();
		XSSFRow row=sheet.getRow(r);
		XSSFCell cell=row.getCell(c);

		printText.setRowspan(result.getRowInc());
		printText.setColspan(result.getColInc());
		
		DataFormatter formatter=new DataFormatter();
		String value=formatter.formatCellValue(cell, evaluator);
		printText.setText(value);

		XSSFCellStyle style=cell.getCellStyle();
		if(style!=null){
			printText.setLineBox(toPrintBorder(style));
			printText.setBackcolor(toPrintBackgroundColor(cell.getCellStyle()));
			printText.setForecolor(toPrintColor(style));
			printText.setFontKey(toPrintFont(style));
			printText.setHorizontalAlign(toPrintHorizontalAlign(style));
			printText.setVerticalAlign(toPrintVerticalAlign(style));
			printText.setPadding(toPrintPadding(style));
			printText.setRotationValue(toPrintRotation(style));
			printText.setOverflow(toPrintTextStretch(style));
		}
		return printText;
	}
	private static String processPlaceholder(XSSFSheet sheet,String s){
		return s.replaceAll("&D", (new SimpleDateFormat("yyyy-MM-dd")).format(new Date()))
				.replaceAll("&T", (new SimpleDateFormat("HH:mm:ss")).format(new Date()))
				.replaceAll("&F", sheet.getSheetName())
				.replaceAll("&A", sheet.getSheetName())
				.replaceAll("&P", "\\$V{PAGE_SIZE}") //当前页码(page index)，不要被字面含义误导
				.replaceAll("&N", "\\$V{PAGE_NUMBER}"); //总页数，不要被字面含义误导
	}
	private static DesignBand toPrintPageHeader(XSSFSheet sheet,int dpi){
		DesignBand printBand = new DesignBand();
		printBand.addColumnWidth(33, LayoutEnum.PERCENT);
		printBand.addColumnWidth(34, LayoutEnum.PERCENT);
		printBand.addColumnWidth(33, LayoutEnum.PERCENT);
		DesignRow printRow = printBand.addRow();
		printRow.setHeight((int)((sheet.getMargin(XSSFSheet.TopMargin)-sheet.getMargin(XSSFSheet.HeaderMargin))*dpi),LayoutEnum.FIXED);
		FontKey font=toPrintFont(sheet.getWorkbook().getFontAt((short) 0));
		//left
		{
			DesignText printCell=new DesignText();
			printCell.setVerticalAlign(VerticalAlignEnum.TOP);
			printCell.setHorizontalAlign(HorizontalAlignEnum.LEFT);
			printCell.setOverflow(OverflowEnum.STRETCH);
			printCell.setFontKey(font);
			String value=processPlaceholder(sheet,sheet.getHeader().getLeft());
			if(value.contains("$V{PAGE_SIZE}") || value.contains("$V{PAGE_NUMBER}")){
				printCell.setExpression(new DesignExpression(value));
				printCell.setEvaluationTime(EvaluationTimeEnum.END);
			} else {
				printCell.setText(value);
			}
			printRow.addCell(printCell);
		}
		//center
		{
			DesignText printCell=new DesignText();
			printCell.setVerticalAlign(VerticalAlignEnum.TOP);
			printCell.setHorizontalAlign(HorizontalAlignEnum.CENTER);
			printCell.setOverflow(OverflowEnum.STRETCH);
			printCell.setFontKey(font);
			String value=processPlaceholder(sheet,sheet.getHeader().getCenter());
			if(value.contains("$V{PAGE_SIZE}") || value.contains("$V{PAGE_NUMBER}")){
				printCell.setExpression(new DesignExpression(value));
				printCell.setEvaluationTime(EvaluationTimeEnum.END);
			} else {
				printCell.setText(value);
			}
			printRow.addCell(printCell);
		}
		//right
		{
			DesignText printCell=new DesignText();
			printCell.setVerticalAlign(VerticalAlignEnum.TOP);
			printCell.setHorizontalAlign(HorizontalAlignEnum.RIGHT);
			printCell.setOverflow(OverflowEnum.STRETCH);
			printCell.setFontKey(font);
			String value=processPlaceholder(sheet,sheet.getHeader().getRight());
			if(value.contains("$V{PAGE_SIZE}") || value.contains("$V{PAGE_NUMBER}")){
				printCell.setExpression(new DesignExpression(value));
				printCell.setEvaluationTime(EvaluationTimeEnum.END);
			} else {
				printCell.setText(value);
			}
			printRow.addCell(printCell);
		}
		return printBand;
	}
	private static DesignBand toPrintPageFooter(XSSFSheet sheet,int dpi){
		DesignBand printBand = new DesignBand();
		printBand.addColumnWidth(33, LayoutEnum.PERCENT);
		printBand.addColumnWidth(34, LayoutEnum.PERCENT);
		printBand.addColumnWidth(33, LayoutEnum.PERCENT);
		DesignRow printRow = printBand.addRow();
		printRow.setHeight((int)((sheet.getMargin(XSSFSheet.BottomMargin)-sheet.getMargin(XSSFSheet.FooterMargin))*dpi),LayoutEnum.FIXED);
		FontKey font=toPrintFont(sheet.getWorkbook().getFontAt((short) 0));
		//left
		{
			DesignText printCell=new DesignText();
			printCell.setVerticalAlign(VerticalAlignEnum.TOP);
			printCell.setHorizontalAlign(HorizontalAlignEnum.LEFT);
			printCell.setOverflow(OverflowEnum.STRETCH);
			printCell.setFontKey(font);
			String value=processPlaceholder(sheet,sheet.getFooter().getLeft());
			if(value.contains("$V{PAGE_SIZE}") || value.contains("$V{PAGE_NUMBER}")){
				printCell.setExpression(new DesignExpression(value));
				printCell.setEvaluationTime(EvaluationTimeEnum.END);
			} else {
				printCell.setText(value);
			}
			printRow.addCell(printCell);
		}
		//center
		{
			DesignText printCell=new DesignText();
			printCell.setVerticalAlign(VerticalAlignEnum.TOP);
			printCell.setHorizontalAlign(HorizontalAlignEnum.CENTER);
			printCell.setOverflow(OverflowEnum.STRETCH);
			printCell.setFontKey(font);
			String value=processPlaceholder(sheet,sheet.getFooter().getCenter());
			if(value.contains("$V{PAGE_SIZE}") || value.contains("$V{PAGE_NUMBER}")){
				printCell.setExpression(new DesignExpression(value));
				printCell.setEvaluationTime(EvaluationTimeEnum.END);
			} else {
				printCell.setText(value);
			}
			printRow.addCell(printCell);
		}
		//right
		{
			DesignText printCell=new DesignText();
			printCell.setVerticalAlign(VerticalAlignEnum.TOP);
			printCell.setHorizontalAlign(HorizontalAlignEnum.RIGHT);
			printCell.setOverflow(OverflowEnum.STRETCH);
			printCell.setFontKey(font);
			String value=processPlaceholder(sheet,sheet.getFooter().getRight());
			if(value.contains("$V{PAGE_SIZE}") || value.contains("$V{PAGE_NUMBER}")){
				printCell.setExpression(new DesignExpression(value));
				printCell.setEvaluationTime(EvaluationTimeEnum.END);
			} else {
				printCell.setText(value);
			}
			printRow.addCell(printCell);
		}
		return printBand;
	}
	public static Document toPrintDocument(String to,Report report,int dpi) throws Exception{
		//excel
		XSSFWorkbook excel=toExcel(report);
		XSSFSheet sheet=excel.getSheet(to);
		XSSFFormulaEvaluator evaluator=excel.getCreationHelper().createFormulaEvaluator();
		//document
		PageCell page=report.getPage(to);
		DesignDocument printDocument=new DesignDocument(toPrintPager(sheet.getPrintSetup().getPaperSize()));
		printDocument.setName(to);
		//如果包含页眉页脚，则页边距取页眉页脚边距，页眉页脚高度是页边距-页眉页脚边距
		printDocument.setMargin(
				toPrintMargin(page.isIncludePHF()?sheet.getMargin(XSSFSheet.HeaderMargin):sheet.getMargin(XSSFSheet.TopMargin),dpi),
				toPrintMargin(sheet.getMargin(XSSFSheet.RightMargin),dpi), 
				toPrintMargin(page.isIncludePHF()?sheet.getMargin(XSSFSheet.FooterMargin):sheet.getMargin(XSSFSheet.BottomMargin),dpi),
				toPrintMargin(sheet.getMargin(XSSFSheet.LeftMargin),dpi)
		);
		printDocument.setOrientation(toPrintOrientation(sheet.getPrintSetup().getLandscape()));
		int rowCount=report.getRowCount(to);
		int colCount=report.getColCount(to);
		//
		DesignColumnGroup printCols = new DesignColumnGroup();
		for(int c=0;c<colCount;c++){
			int width=getColPixelWidth(sheet,c,page.getFontWidth(),dpi);
			printCols.addColumnWidth(width,page.isDynamicZoom()?LayoutEnum.PERCENT:LayoutEnum.FIXED);
		}
		printDocument.setColumnGroup(printCols);
		//
		Set<ResultCell> visited=new HashSet<ResultCell>();
		Map<String,BaseCellElement> positions=new HashMap<String, BaseCellElement>();
		//
		int titleCount=page.getTitleCount();
		int headerCount=page.getHeaderCount();
		//page-header
		{
			if(page.isIncludePHF()){
				DesignBand printBand = toPrintPageHeader(sheet,dpi);
				printDocument.setPageHeader(printBand);
			}
		}
		//title
		{
			visited.clear();
			DesignBand printBand = new DesignBand();
	        int rowBegin=0;
	        int rowEnd=(rowCount<titleCount)?rowCount:titleCount;
	        for(int r=rowBegin;r<rowEnd;r++){
	        	int height=(int) (getRowPixelHeight(sheet, r, dpi));
	        	DesignRow printRow = printBand.addRow();
	        	printRow.setHeight(height);
	        	for(int c=0;c<colCount;c++){
	        		ResultCell cell=report.getCell(to,r,c);
					if(cell==null){
						DesignText printCell=toPrintCell(sheet,r,c);
		        		printRow.addCell(printCell);
		        		positions.put(r+"-"+c, printCell);
					} else {
						if(visited.contains(cell)){continue;}
						visited.add(cell);
						if(cell instanceof ChartResultCell){
							DesignImage printCell=toPrintCell(sheet,report.getImages(to),cell,r,c);
							printRow.addCell(printCell);
							positions.put(r+"-"+c, printCell);
						} else {
							DesignText printCell=toPrintCell(sheet,evaluator,cell,r,c);
							printRow.addCell(printCell);
							positions.put(r+"-"+c, printCell);
						}
					}
	        	}
	        }
	        printDocument.setTitle(printBand);
		}
        //header
		{
	        visited.clear();
			DesignBand printBand = new DesignBand();
			int rowBegin=titleCount;
	        int rowEnd=((rowCount-titleCount)<headerCount)?rowCount:(titleCount+headerCount);
	        for(int r=rowBegin;r<rowEnd;r++){
	        	int height=(int) (getRowPixelHeight(sheet, r, dpi));
	        	DesignRow printRow = printBand.addRow();
	        	printRow.setHeight(height);
	        	for(int c=0;c<colCount;c++){
	        		ResultCell cell=report.getCell(to,r,c);
					if(cell==null){
						DesignText printCell=toPrintCell(sheet,r,c);
		        		printRow.addCell(printCell);
		        		positions.put(r+"-"+c, printCell);
					} else {
						if(visited.contains(cell)){continue;}
						visited.add(cell);
						if(cell instanceof ChartResultCell){
							DesignImage printCell=toPrintCell(sheet,report.getImages(to),cell,r,c);
							printRow.addCell(printCell);
							positions.put(r+"-"+c, printCell);
						} else {
							DesignText printCell=toPrintCell(sheet,evaluator,cell,r,c);
							printRow.addCell(printCell);
							positions.put(r+"-"+c, printCell);
						}
					}
	        	}
	        }
	        printDocument.setColumnHeader(printBand);
		}
        //detail
		{
			visited.clear();
			DesignBand printBand = new DesignBand();
			int rowBegin=titleCount+headerCount;
			int rowEnd=rowCount;
	        for(int r=rowBegin;r<rowEnd;r++){
	        	int height=(int) (getRowPixelHeight(sheet, r, dpi));
	        	DesignRow printRow = printBand.addRow();
	        	printRow.setHeight(height);
	        	for(int c=0;c<colCount;c++){
	        		ResultCell cell=report.getCell(to,r,c);
					if(cell==null){
						DesignText printCell=toPrintCell(sheet,r,c);
		        		printRow.addCell(printCell);
		        		positions.put(r+"-"+c, printCell);
					} else {
						if(visited.contains(cell)){continue;}
						visited.add(cell);
						if(cell instanceof ChartResultCell){
							DesignImage printCell=toPrintCell(sheet,report.getImages(to),cell,r,c);
							printRow.addCell(printCell);
							positions.put(r+"-"+c, printCell);
						} else {
							DesignText printCell=toPrintCell(sheet,evaluator,cell,r,c);
							printRow.addCell(printCell);
							positions.put(r+"-"+c, printCell);
						}
					}
	        	}
	        }
	        printDocument.setDetail(printBand);
		}
		//page-footer
		{
			if(page.isIncludePHF()){
				DesignBand printBand = toPrintPageFooter(sheet,dpi);
				printDocument.setPageFooter(printBand);
			}
		}
		//float
		{
			Map<ResultCell,byte[]> images=report.getImages(to);
			Set<ResultCell> floatings=report.getImages(to).keySet();
			for(ResultCell floating:floatings){
				if(floating instanceof FloatingResultCell){
					ResultCell cell=report.getCell(to,floating.getRbegin(), floating.getCbegin());
					BaseCellElement printCell=null;
					int offsetX=0;
					int offsetY=0;
					if(cell==null){
						printCell=positions.get(floating.getRbegin()+"-"+floating.getCbegin());
					} else {
						printCell=positions.get(cell.getRbegin()+"-"+cell.getCbegin());
						for(int r=cell.getRbegin();r<floating.getRbegin();r++){
							offsetY=offsetY+(int)(getRowPixelHeight(sheet, r, dpi));
						}
						for(int c=cell.getCbegin();c<floating.getCbegin();c++){
							offsetX=offsetX+(int)(getColPixelWidth(sheet,c,page.getFontWidth(),dpi));
						}
					}
					byte[] png=images.get(floating);
					ByteArrayInputStream in=new ByteArrayInputStream(png);
					ByteArrayOutputStream out=new ByteArrayOutputStream();
					Thumbnails.of(in).scale(0.75f).outputFormat("png").toOutputStream(out);
					BytesImageStore store = new BytesImageStore();
					store.setImageType(ImageTypeEnum.PNG);
					store.writeBytes(out.toByteArray());
					DesignImage printImage = new DesignImage();
					printImage.setHorizontalAlign(HorizontalAlignEnum.LEFT);
					printImage.setVerticalAlign(VerticalAlignEnum.TOP);
					printImage.setScaleImage(ScaleImageEnum.REALSIZE);
					printImage.setX(offsetX);
					printImage.setY(offsetY);
					printImage.setImageStore(store);
					printImage.setFlying(true);//盖到字上
					if(printCell!=null){
						printCell.setStampElement(printImage);
					}
				}
			}
		}
		visited.clear();
		positions.clear();
		/*
		java.io.FileOutputStream file=new java.io.FileOutputStream("/sofa/print/"+report.getName()+".obj");
		java.io.ObjectOutputStream output=new java.io.ObjectOutputStream(file);
		try {
			output.writeObject(printDocument);
		} finally {
			output.close();
			file.close();
		}
		*/
		return printDocument;
	}
	public static Map<String,Document> toPrintDocuments(Report report,int dpi) throws Exception{
		Map<String,Document> result=new LinkedHashMap<String,Document>();
		//excel
		XSSFWorkbook excel=toExcel(report);
		XSSFFormulaEvaluator evaluator=excel.getCreationHelper().createFormulaEvaluator();
		Collection<String> tos=report.getToSheetNames();
		for(String to:tos){
    		XSSFSheet sheet=excel.getSheet(to);
    		//document
    		PageCell page=report.getPage(to);
    		DesignDocument printDocument=new DesignDocument(toPrintPager(sheet.getPrintSetup().getPaperSize()));
    		printDocument.setName(to);
    		//如果包含页眉页脚，则页边距取页眉页脚边距，页眉页脚高度是页边距-页眉页脚边距
    		printDocument.setMargin(
    				toPrintMargin(page.isIncludePHF()?sheet.getMargin(XSSFSheet.HeaderMargin):sheet.getMargin(XSSFSheet.TopMargin),dpi),
    				toPrintMargin(sheet.getMargin(XSSFSheet.RightMargin),dpi), 
    				toPrintMargin(page.isIncludePHF()?sheet.getMargin(XSSFSheet.FooterMargin):sheet.getMargin(XSSFSheet.BottomMargin),dpi),
    				toPrintMargin(sheet.getMargin(XSSFSheet.LeftMargin),dpi)
    		);
    		printDocument.setOrientation(toPrintOrientation(sheet.getPrintSetup().getLandscape()));
    		int rowCount=report.getRowCount(to);
    		int colCount=report.getColCount(to);
    		//
    		DesignColumnGroup printCols = new DesignColumnGroup();
    		for(int c=0;c<colCount;c++){
    			int width=getColPixelWidth(sheet,c,page.getFontWidth(),dpi);
    			printCols.addColumnWidth(width,page.isDynamicZoom()?LayoutEnum.PERCENT:LayoutEnum.FIXED);
    		}
    		printDocument.setColumnGroup(printCols);
    		//
    		Set<ResultCell> visited=new HashSet<ResultCell>();
    		Map<String,BaseCellElement> positions=new HashMap<String, BaseCellElement>();
    		//
    		int titleCount=page.getTitleCount();
    		int headerCount=page.getHeaderCount();
    		//page-header
    		{
    			if(page.isIncludePHF()){
    				DesignBand printBand = toPrintPageHeader(sheet,dpi);
    				printDocument.setPageHeader(printBand);
    			}
    		}
    		//title
    		{
    			visited.clear();
    			DesignBand printBand = new DesignBand();
    	        int rowBegin=0;
    	        int rowEnd=(rowCount<titleCount)?rowCount:titleCount;
    	        for(int r=rowBegin;r<rowEnd;r++){
    	        	int height=(int) (getRowPixelHeight(sheet, r, dpi));
    	        	DesignRow printRow = printBand.addRow();
    	        	printRow.setHeight(height);
    	        	for(int c=0;c<colCount;c++){
    	        		ResultCell cell=report.getCell(to,r,c);
    					if(cell==null){
    						DesignText printCell=toPrintCell(sheet,r,c);
    		        		printRow.addCell(printCell);
    		        		positions.put(r+"-"+c, printCell);
    					} else {
    						if(visited.contains(cell)){continue;}
    						visited.add(cell);
    						if(cell instanceof ChartResultCell){
    							DesignImage printCell=toPrintCell(sheet,report.getImages(to),cell,r,c);
    							printRow.addCell(printCell);
    							positions.put(r+"-"+c, printCell);
    						} else {
    							DesignText printCell=toPrintCell(sheet,evaluator,cell,r,c);
    							printRow.addCell(printCell);
    							positions.put(r+"-"+c, printCell);
    						}
    					}
    	        	}
    	        }
    	        printDocument.setTitle(printBand);
    		}
            //header
    		{
    	        visited.clear();
    			DesignBand printBand = new DesignBand();
    			int rowBegin=titleCount;
    	        int rowEnd=((rowCount-titleCount)<headerCount)?rowCount:(titleCount+headerCount);
    	        for(int r=rowBegin;r<rowEnd;r++){
    	        	int height=(int) (getRowPixelHeight(sheet, r, dpi));
    	        	DesignRow printRow = printBand.addRow();
    	        	printRow.setHeight(height);
    	        	for(int c=0;c<colCount;c++){
    	        		ResultCell cell=report.getCell(to,r,c);
    					if(cell==null){
    						DesignText printCell=toPrintCell(sheet,r,c);
    		        		printRow.addCell(printCell);
    		        		positions.put(r+"-"+c, printCell);
    					} else {
    						if(visited.contains(cell)){continue;}
    						visited.add(cell);
    						if(cell instanceof ChartResultCell){
    							DesignImage printCell=toPrintCell(sheet,report.getImages(to),cell,r,c);
    							printRow.addCell(printCell);
    							positions.put(r+"-"+c, printCell);
    						} else {
    							DesignText printCell=toPrintCell(sheet,evaluator,cell,r,c);
    							printRow.addCell(printCell);
    							positions.put(r+"-"+c, printCell);
    						}
    					}
    	        	}
    	        }
    	        printDocument.setColumnHeader(printBand);
    		}
            //detail
    		{
    			visited.clear();
    			DesignBand printBand = new DesignBand();
    			int rowBegin=titleCount+headerCount;
    			int rowEnd=rowCount;
    	        for(int r=rowBegin;r<rowEnd;r++){
    	        	int height=(int) (getRowPixelHeight(sheet, r, dpi));
    	        	DesignRow printRow = printBand.addRow();
    	        	printRow.setHeight(height);
    	        	for(int c=0;c<colCount;c++){
    	        		ResultCell cell=report.getCell(to,r,c);
    					if(cell==null){
    						DesignText printCell=toPrintCell(sheet,r,c);
    		        		printRow.addCell(printCell);
    		        		positions.put(r+"-"+c, printCell);
    					} else {
    						if(visited.contains(cell)){continue;}
    						visited.add(cell);
    						if(cell instanceof ChartResultCell){
    							DesignImage printCell=toPrintCell(sheet,report.getImages(to),cell,r,c);
    							printRow.addCell(printCell);
    							positions.put(r+"-"+c, printCell);
    						} else {
    							DesignText printCell=toPrintCell(sheet,evaluator,cell,r,c);
    							printRow.addCell(printCell);
    							positions.put(r+"-"+c, printCell);
    						}
    					}
    	        	}
    	        }
    	        printDocument.setDetail(printBand);
    		}
    		//page-footer
    		{
    			if(page.isIncludePHF()){
    				DesignBand printBand = toPrintPageFooter(sheet,dpi);
    				printDocument.setPageFooter(printBand);
    			}
    		}
    		//float
    		{
    			Map<ResultCell,byte[]> images=report.getImages(to);
    			Set<ResultCell> floatings=report.getImages(to).keySet();
    			for(ResultCell floating:floatings){
    				if(floating instanceof FloatingResultCell){
    					ResultCell cell=report.getCell(to,floating.getRbegin(), floating.getCbegin());
    					BaseCellElement printCell=null;
    					int offsetX=0;
    					int offsetY=0;
    					if(cell==null){
    						printCell=positions.get(floating.getRbegin()+"-"+floating.getCbegin());
    					} else {
    						printCell=positions.get(cell.getRbegin()+"-"+cell.getCbegin());
    						for(int r=cell.getRbegin();r<floating.getRbegin();r++){
    							offsetY=offsetY+(int)(getRowPixelHeight(sheet, r, dpi));
    						}
    						for(int c=cell.getCbegin();c<floating.getCbegin();c++){
    							offsetX=offsetX+(int)(getColPixelWidth(sheet,c,page.getFontWidth(),dpi));
    						}
    					}
    					byte[] png=images.get(floating);
    					ByteArrayInputStream in=new ByteArrayInputStream(png);
    					ByteArrayOutputStream out=new ByteArrayOutputStream();
    					Thumbnails.of(in).scale(0.75f).outputFormat("png").toOutputStream(out);
    					BytesImageStore store = new BytesImageStore();
    					store.setImageType(ImageTypeEnum.PNG);
    					store.writeBytes(out.toByteArray());
    					DesignImage printImage = new DesignImage();
    					printImage.setHorizontalAlign(HorizontalAlignEnum.LEFT);
    					printImage.setVerticalAlign(VerticalAlignEnum.TOP);
    					printImage.setScaleImage(ScaleImageEnum.REALSIZE);
    					printImage.setX(offsetX);
    					printImage.setY(offsetY);
    					printImage.setImageStore(store);
    					printImage.setFlying(true);//盖到字上
    					if(printCell!=null){
    						printCell.setStampElement(printImage);
    					}
    				}
    			}
    		}
    		visited.clear();
    		positions.clear();
    		/*
    		java.io.FileOutputStream file=new java.io.FileOutputStream("/sofa/print/"+report.getName()+".obj");
    		java.io.ObjectOutputStream output=new java.io.ObjectOutputStream(file);
    		try {
    			output.writeObject(printDocument);
    		} finally {
    			output.close();
    			file.close();
    		}
    		*/
    		result.put(to, printDocument);
		}
		return result;
	}
}
